package project.mapper;


import project.common.DateFormatUtiles;
import project.common.JDBCUtils;
import project.entity.po.User;
import project.entity.vo.UserVO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;


public class UserDaoImpl implements UserDao {


    @Override
    public void deleteById(Integer id) {

    }

    //插入一个完整的用户信息到数据库里面
    @Override
    public int insert(User user) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-ddTHH:mm:ss");
        int i = 0;
        try {
            ps = connection.prepareStatement("insert into user(name,password,create_time,update_time,role_id) value(?,?,?,?,?)");
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getCreateTime().format(df));
            ps.setString(4, user.getUpdateTime().format(df));
            ps.setInt(5, user.getRoleId());
            i = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, null);
        }
        return i;
    }

    //通过名字搜索召唤师，并返回召唤师对象
    @Override
    public User selectUserByName(String name) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            ps = connection.prepareStatement("select * from user where name = ? and is_deleted = 0");
            ps.setString(1, name);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User(rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("password"),
                        rs.getInt("role_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, rs);
        }
        return user;
    }

    //根据召唤师id查询召唤师
    @Override
    public User selectUserById(Integer id) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;

        try {
            ps = connection.prepareStatement("select * from user where id=? and is_deleted=0");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User(rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("password"),
                        rs.getInt("role_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, rs);
        }
        return user;
    }

    @Override
    public void deleteUserById(Integer id) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement("update user set is_deleted=1 where id =?");
            ps.setInt(1, id);
            ps.executeUpdate();
            System.out.println("删除成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, null);
        }
    }

    @Override
    public void deleteUserByName(String name) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement("update user set is_deleted=1 where name=?");
            ps.setString(1, name);
            ps.executeUpdate();
            System.out.println("删除成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, null);
        }
    }

    @Override
    public void changePassword(String name, String newPassword) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("update user set password=? where name=? and is_deleted=0");
            ps.setString(1, newPassword);
            ps.setString(2, name);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, null);
        }
    }

    //找到所有召唤师，用对象存储，不分页，存进集合里面并返回集合
    @Override
    public List<UserVO> selectAllUsers(List<UserVO> list) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;

        try {
            ps = connection.prepareStatement("select u.*,r.name rname from user u , role r where u.role_id = r.id and u.is_deleted=0");
            rs = ps.executeQuery();

            while (rs.next()) {
                list.add(new UserVO(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("password"),
                        DateFormatUtiles.dateFormat(rs.getString("create_time")),
                        DateFormatUtiles.dateFormat(rs.getString("update_time")),
                        rs.getString("rname")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, rs);
        }
        return list;
    }

    //通过名字和密码搜索召唤师是否存在，密码是否正确
    @Override
    public Boolean selectUserIsHaveByNameAndPassword(String name, String password) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select password from user where name = ?");
            ps.setString(1, name);
            rs = ps.executeQuery();
            String pw = null;
            if (rs.next()) {
                pw = rs.getString("password");
            }
            if (pw.equals(password)) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, rs);
        }
        return false;
    }

    //分页查询所有召唤师代码
    @Override
    public List<User> selectAllUsersPaging(int pageNo, int pageSize) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            ps = connection.prepareStatement("select * from user where is_deleted=0 limit ?,?");
            ps.setInt(1, (pageNo - 1) * pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("password"),
                        rs.getInt("role_id")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, rs);
        }
        return list;
    }

    @Override
    public Integer getCount() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer count = 0;
        try {
            ps = connection.prepareStatement("select count(*) as c from user where is_deleted=0");
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, rs);
        }
        return count;
    }

    @Override
    public void test() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(" select create_time from user");
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("create_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, ps, rs);
        }
    }


}
